Back to Main Menu

Creating a Local Backup of Assetic Data (Assetic Python SDK)

This article will demonstrate the use of a python script to automate SQL Server database syncing. The underlying process the script relies on is the functionality to bulk export using the advanced search feature. For an overview of using Advanced Search to bulk export data, refer to this article on the Integration page.

Local SQL Server database sync using Assetic Python SDK

This process is used to export Assetic data from Assetic to a local SQL Server database.

The data to be exported is defined via Search Profiles created via the Advanced Search.

The Assetic Python sync process wraps the Assetic REST API for bulk exporting search profiles and manages the updating of the local SQL Server database.
The database is defined when running the sync process.
The sync process needs to be scheduled to run via a scheduler such as the Windows Task Scheduler.

Where more than 10,000 records are returned by the Search Profile the export becomes a 2-step process since it utilises the Assetic background worker process to generate the export.  For less than 10,000 the export is immediate unless specified otherwise using one of the parameters available when initiating the export.

For search profiles that include a last modified date field, there is a process that allows the export to be skipped if the data hasn't changed (the actual field name for the last modified date differs between modules)

Quick Start

See below for 'Advanced Options' options

1. Create management table "assetic_sync_manager"

The sync process utilises a table called "assetic_sync_manager". This table must be in the same database as the target table.

The table may be created via the following SQL:

CREATE TABLE assetic_sync_manager(  [oid]  [uniqueidentifier] NULL,
  [taskid]  [nvarchar](50) NULL,
  [profileid]  [nvarchar](50) NULL,
  [profilename]  [nvarchar](100) NULL,
  [tablename]  [nvarchar](50) NULL,
  [keyfield]  [nvarchar](50) NULL,
  [logtable]  [nvarchar](50) NULL,
  [documentid]  [nvarchar](50) NULL,
  [useinternalnames]  [bit] NULL,
  [replacespaces]  [bit] NULL,
  [spacedelimiter]  [nvarchar](50) NULL,
  [setemptyasnull]  [bit] NULL,
  [allowdelete]  [bit] NULL,
  [date_initiated]  [datetime] NULL,
  [date_last_modified]  [datetime] NULL,
  [status]  [nvarchar](50) NULL,
  [replace_special]  [nvarchar](500) NULL,
  [truncation_indicator]  [nvarchar](50) NULL,
  [total_records]  [int] NULL,
  [processed_records]  [int] NULL )

2. Define sync parameters

The sync process requires information about the search profile(s).
This definition is provided via the Assetic Python SDK object "SearchProfileRepresentation". The object has the following fields:

3. Initiate the sync

The sync process is initiated via the Assetic Python SDK method SyncToLocalProcesses.dbsync_initiate().
It has the following mandatory parameters:
dbserver: database instance (SQL Server)
dbname: database name. Assumes trusted connection
profiles: an array of SearchProfileRepresentation instances

There are also 6 optional parameters:
incremental: This is a boolean to indicate whether there should be a full export of all data, or just an export of changes since the last date. This option is currently disabled, the sync always performs a full export (i.e. incremental = False)

username: SQL Server login username

password: SQL Server password

driver: SQL Server ODBC driver. The default is "ODBC Driver 13 for SQL Server" (Assumes this driver is installed)

custom_field_list: Use to set a database field name that cannot be generated by the default field name options. Refer to 'Advanced Options' options for further detail.

force_export: Force the export to be initialised even if there have been no changes to the records. Useful if the number of fields in the search profile has changed. Not relevant if the search profile doesn't contain a 'last modified' field.

always_background_export: The default is "False". If "True" then all exported profiles will initiate via an Assetic background worker process. This can be used to ensure that all search profile exports will have a corresponding export file, including search profiles with less than 10,000 records that would otherwise be created immediately.

The following example creates 2 separate export jobs for 2 different search profiles

"""
Assetic.BulkExportInitialise.py
  
Initialises the bulk export process.
Define a set of search profiles to be exported, and the corresponding db tables
to write the data to, and log changes to
When this py script is run it uses the Assetic 'export all' api to initiate the
export, which uses the Assetic background worker to queue and execute the export
A second 'finalise' step run via a separate py script to check to see if the
export is complete, and if so downloads the exported data
Assumes the user running the script can establish a connection with the
database
Full documentation: https://assetic.zendesk.com/hc/en-us/articles/236156708-Bulk-Export-Process-Assetic-Python-SDK-
Author: Kevin Wilton (Assetic)
"""
import assetic
# Define location of configuration file, log file, log level
asseticsdk = assetic.AsseticSDK("C:/users/myself/assetic.ini",  None,  "Error")
  
sync = assetic.SyncToLocalProcesses()
  
# Define an empty list of profiles
profiles =  []
  
# Profile for 'Assets Sync'
profilemapping = assetic.SearchProfileRepresentation()
profilemapping.profileguid =  "d9a350d7-8078-e611-946c-06edd62954d7"
profilemapping.profilename =  "Water Pressure Pipes"
profilemapping.tablename =  "waterpressurepipes"
profilemapping.keyfield =  "[Asset Id]"
profilemapping.logtable =  "waterpressurepipes_log"
profilemapping.useinternalnames =  False
# Use field labels which will have whitespaces, so remove the whitespaces
profilemapping.replacespaces =  True
profilemapping.spacedelimiter =  "_"  # Replace whitespace with "_"
# If the data is an empty string set it as NULL in the database
profilemapping.set_empty_as_null =  True
# If a record is no longer in the Assetic data then remove it from the DB
profilemapping.allow_delete =  True
# The column name may have characters such as "/".  Replace with a "_"
profilemapping.replace_special_characters =  {
  "(":  "_",
  ")":  "_",
  "-":  "_",
  "/":  "_"
  }
# Rather than ensuring the target columns are long enough, truncate the data
profilemapping.truncation_indicator =  "..."
# If a profile has less than 10,000 records, force it to export via background worker
profilemapping.background_export =  True
# add profile definition to profiles list
profiles.append(profilemapping)
  
# Profile for 'Component Sync'
profilemapping = assetic.SearchProfileRepresentation()
profilemapping.profileguid =  "65ffd995-4e76-e611-946c-06edd62954d7"
profilemapping.profilename =  "All Components"
profilemapping.tablename =  "allcomponents"
profilemapping.keyfield =  "[Component Id]"
profilemapping.logtable =  "allcomponents_log"
profilemapping.useinternalnames =  True
profilemapping.background_export =  False
profiles.append(profilemapping)
  
# Initialise the export.
sync.dbsync_initiate('dbserver',  'asseticsync', profiles,
                     force_export=False, always_background_export=False)

This process may be scheduled to run on a periodic basis, such as weekly

4.  Finalise sync

The Assetic Python SDK method SyncToLocalProcesses.dbsync_finalise() is used to check the status of export tasks, and if the export task is complete, the CSV file data is downloaded and applied to the target table in the database. The database table assetic_sync_manager is used to get a list of all tasks that are flagged as 'In Process'.

The "dbsync_finalise" method is also run as a scheduled process (at a later time to the SyncToLocalProcesses.dbsync_initiate) as there is no "notification service" to trigger the finalisation.

SyncToLocalProcesses.dbsync_finalise has the following mandatory parameters:

dbserver: database instance (SQL Server)

dbname: database name. Assumes trusted connection

There are also 4 optional parameters:
username: SQL Server login username

password: SQL Server password

driver: SQL Server ODBC driver. The default is "ODBC Driver 13 for SQL Server" (Assumes this driver is installed)

custom_field_list: Use to set a database field name that cannot be generated by the default field name options. Refer to 'Advanced Options' options for further detail.

In the example below it is assumed the user is able to make a trusted connection to the database, and that the driver "ODBC Driver 13 for SQL Server" is installed.

"""
Assetic.BulkExportFinalise.py
  
Finalises the bulk export process.
  
When this py script is run it checks to see if there are any outstanding bulk
exports awaiting download.  This is indicated by the database table
assetic_sync_manager where status of an export is 'In Progress'
The Assetic API's are used to check if the export is ready for download and if
so, the data is downloaded and sync with the current data.
The 'initialise' process defines the database table to sync to
Full documentation: https://assetic.zendesk.com/hc/en-us/articles/236156708-Bulk-Export-Process-Assetic-Python-SDK-
Author: Kevin Wilton (Assetic)
"""
import assetic
# Set logging to "Info" level
asseticsdk = assetic.AsseticSDK("C:/Users/myself/assetic.ini",  None,  "Info")
sync = assetic.SyncToLocalProcesses()
# Using trusted connection
response = sync.dbsync_finalise("dbserver",  "asseticsync")

As with the script to initiate the process, if the driver "ODBC Driver 13 for SQL Server" is not installed the driver may be specified. Also if an SQL Server username and password login are to be used instead of a trusted connection then this may be specified. In the example below the driver "SQL Server Native Client 11.0" is specified along with username and password.

sync.dbsync_finalise('dbserver','asseticsync',username='fred',password='mypwd',
 driver="ODBC Driver 11 for SQL Server")

How it works

The process uses the Assetic Python SDK. It is first loaded and authenticated via the ini file (first parameter). The second parameter is the name of the log file. The third parameter is the logging level - an option selected from 'debug', 'warning' or 'info'.

import assetic ##initialise Assetic Python SDK.  Set logging mode to 'info' asseticsdk = assetic.AsseticSDK('C:/Users/myself/assetic.ini',None,'info')

An instance of the Assetic SDK "SyncToLocalProcesses" library is then initiated

sync = assetic.SyncToLocalProcesses()

The search profiles to be exported are defined as an array:

# define an empty list of profiles profiles =  []

For each search profile, the export parameters are defined

##create a profile instance and set values profilemapping = assetic.SearchProfileRepresentation() profilemapping.profileguid =  "d9a350d7-8078-e611-946c-06edd62954d7" profilemapping.profilename =  "Water Pressure Pipes" profilemapping.tablename =  "waterpressurepipes" profilemapping.keyfield =  "[Asset Id]" profilemapping.logtable =  "waterpressurepipes_log" profilemapping.useinternalnames =  False  # Use field labels which will have whitespaces, so remove the whitespaces profilemapping.replacespaces =  True profilemapping.spacedelimiter =  "_"  # Replace whitespace with "_"  # If the data is an empty string set it as NULL in the database profilemapping.set_empty_as_null =  True  # If a record is no longer in the Assetic data then remove it from the DB profilemapping.allow_delete =  True  # The column name may have characters such as "/".  Replace with a "_" profilemapping.replace_special_characters =  {  "(":  "_",  ")":  "_",  "-":  "_",  "/":  "_"  }  # Rather than ensuring the target columns are long enough, truncate the data profilemapping.truncation_indicator =  "..."
# If a profile has less than 10,000 records, force it to export via background worker
profilemapping.background_export =  True

The profile is then added to the array

# add profile definition to profiles list profiles.append(profilemapping)

The following code initiates the export task with the parameters defined in the table below

# Initialise the export. 
sync.dbsync_initiate('dbserver',  'asseticsync', profiles, force_export=False, always_background_export=False)

dbsync_initiate uses the Assetic REST API endpoint POST /api/v2/search/{id}/export to create a new export task. The ID of the task is recorded in the table assetic_sync_manager.

Since the background worker process queues jobs, the export may not be created immediately.

Export skipped if no changes

The export process will be skipped if:

• The target database table is not empty and contains records (a comparison is not made of the number of records in the database compared to the search)
• and the number of records returned by the search since the last export hasn't changed
• and the search profile contains a 'last modified date' field and there a no records with a more recent last modified date that the DateTime the export was last started (the start time of the background process rather than the time that the process was added to the queue).

The name of the 'last modified date' field differs between search modules as indicated by the table below. The process also relies on being the key field being set according to the module to allow the correct name for the date field to be applied.

Finalise Export

The sync "dbsyc_finalise" process is run after the "dbsync_initiate" process, allowing time in between for the background worker to process the export. The "dbsync_finalise" may be run several times since it only processes completed exports that are flagged as outstanding.

Using the Assetic REST API endpoint GET /api/v2/backgroundworker/{id} this taskid can be used to check the status of the export task.  If the task is complete, the GET /api/v2/backgroundworker/{id} response also returns the document GUID of the CSV file.

The Assetic REST API endpoint GET /api/v2/document/{id}/file is used by "dbsync_finalise" to get the csv document. It doesn't however need to save the file to disk since it is read into memory as part of the document GET.

"dbsync_finalise" is initiated by first importing the Assetic Python SDK and authenticating via the ini file (first parameter). The second parameter is the name of the log file. The third parameter is the logging level - one of 'debug', 'warning' or 'info'

import assetic # initialise Assetic Python SDK.  Set logging mode to 'info' asseticsdk=assetic.AsseticSDK('C:/Users/me/assetic.ini','c:/logs/dbsync.log','info')

An instance of the Assetic SDK "SyncToLocalProcesses" library is then initiated

sync = assetic.SyncToLocalProcesses()

The sync finalise process is then run. It only requires the database server and database name since the remaining information required is held in the table assetic_sync_manager

#execute sync finalise sync.dbsync_finalise('dbserver','asseticsync')

The downloaded CSV file data is merged into the target database table. This relies on a "key field" being correctly defined

• New records are appended to the target database table. A new record is a record that doesn't have a corresponding key in the target database table
• Edited records are updated in the target table. Edits are determined on the basis of comparing each field in the record downloaded from Assetic with the corresponding record in the target database table.
• Deleted records are optionally removed from the target database table. A deleted record is determined on the basis that it is in the target database table but not the records downloaded from Assetic. Deleting is optional because an alternative approach is to include the field that records the status of the record in Assetic in the export process. That way the status can be used within SQL queries to either include or exclude deleted records.

For each of the actions above, the "log" table is appended to. The log table typically has the same structure as the target table, plus additional columns for the edit action and sync date. If the log table does not exist it will be created to match the target table.

• For a new record, the log table will just be populated with the edit action "INSERTED", the timestamp, and the key field value for the new record.
• For an existing record, the log table will be populated with the key edit action "UPDATED", the timestamp, plus the values of the record prior to the update.
• For a deleted record, the log table will be populated with the key edit action "DELETED", the timestamp, plus the values of the record prior to the delete.

The database table assetic_sync_manager is then updated with the status 'Complete', and the 'date_last_modified' field is updated with the current DateTime.

Advanced Options

Target Table

The target table is the database table that the data from the search export is written to. The target table is built automatically if it does not already exist. The column names in the table are the field names from the search profile. If the SearchProfileRepresentation property 'useinternalnames' is TRUE then the column names are based on the Assetic internal field names, otherwise, the column names are based on the user-friendly search field names. The target table is built using the metadata for the search profile, via the Assetic REST endpoint GET /api/v2/search/{id}/metadata. The response from this search includes:

If required, the Assetic Python SDK method SyncToLocalProcesses.build_table_from_search may be used with the optional parameter printonly=TRUE to print the SQL that will be used to create the table. The SQL can then be manually edited to more closely match data types and field lengths.  The SQL is then applied to create the table.

Sample script:

# Assetic.BulkExportTableCreation
# Create the target database table for a given search profile
import assetic
  
# initialise Assetic Python SDK.  Set logging mode to 'info'
asseticsdk = assetic.AsseticSDK('C:/Users/myself/assetic.ini',  None,  'info')
  
sync = assetic.SyncToLocalProcesses()
# database connection required.
# Database will be tested for existence of the target table and script will not
# be created if table exists
dbtools = assetic.DB_Tools('dbserver',  'assetic_sync')
  
# define the profile that the table SQL create syntax is to be built for
profilemapping = assetic.SearchProfileRepresentation()
profilemapping.profileguid =  "52e11423-dc76-e511-944d-06ed862954d5"
profilemapping.profilename =  "Component Sync"
profilemapping.tablename =  "expcomp"
profilemapping.keyfield =  "[Component ID]"
profilemapping.logtable =  "expcomp_log"
profilemapping.useinternalnames =  False
profilemapping.replacespaces =  True
profilemapping.spacedelimiter =  "_"
profilemapping.set_empty_as_null =  True
profilemapping.allow_delete =  True
# note that changing profilemapping.useinternalnames = TRUE changes
# the SQL created to use Assetic internal field names for database column names
  
# get column mappings and data types for search profile and build table
sync.build_table_from_search(dbtools,  None, profilemapping,  True)

Custom Field Names

A list of assetic.SyncProcessCustomFieldRepresentation instances may be specified via the optional parameter 'custom_field_list' when executing both SyncToLocalProcesses.dbsync_initiate() and SyncToLocalProcesses.dbsync_finalise().

For each search profile, the database field name can be changed from the default value to a custom value.  This setting will override any generated field name based on the parameters 'useinternalnames', 'replacespaces', and 'spacedelimiter'.

assetic.SyncProcessCustomFieldRepresentation has the following structure

custom_field_list =  []  # set some custom field names custom_fields = assetic.SyncProcessCustomFieldRepresentation custom_fields.profileguid =  "e9a350d7-8078-e611-946c-06edd62954d6" fields = dict() fields["Asset_Zone"]  =  "TheAssetZone" fields["OCI"]  =  "TheOC" custom_fields.custom_field_names = fields # Append the setting to the list custom_field_list.append(custom_fields)

sync.dbsync_initiate("localhost","scratch",profiles,custom_field_list=custom_field_list)

sync.dbsync_finalise("localhost","scratch", custom_field_list= custom_field_list)

Log Table

The log table will be in the same database as the target table.

A record is added to this table for every new record inserted into the target database, and every existing record that is updated. When a record is updated, the values of the record prior to the update are copied to the log table. So the target table always contains the most current data, and the log table contains archived data.

The log table may be manually created by using the target table as a template and must also include the following additional fields:
[action] [nvarchar](10)
[syncdate] [datetime]

assetic_sync_manager table

The assetic_sync_manager table is used to manage the sync process.

Fields:

When a process is initiated via SyncToLocalProcesses.dbsync_initiate the task id of the process is written to the field 'taskid'. A unique identifier is also generated and defined for each task in the field 'oid'.

The timestamp of when the task was initiated is recorded in the field 'date_initiated

The Assetic Python SDK method SyncToLocalProcesses.build_assetic_sync_manager_table may be used to create the table. It requires the following parameters:
dbserver: the name of the SQL Server instance
dbname: the name of the SQL Server database
It assumes the user is able to make a trusted connection to the database.

Search Profile Export

The Assetic REST API endpoint POST /api/v2/search/{id}/export initiates an export of the search results for the given search profile guid "{id}". No search filters can be set via the API, so the search profile must already have search filters defined within the profile.

This API provides the same functionality as the 'Export All' button seen in the results grid of Advanced Search profiles within the Assetic application.
The POST response is the guid of the export task. This guid is used to retrieve the status and document ID of the export task via the Assetic REST API endpoint GET /api/v2/backgroundworker/{id}.

Sample payload

https://abc.assetic.net/api/v2/search/b856b2e6-ffbc-e611-946c-06edd62954d7/export

Background Worker API

The Assetic REST API endpoint GET /api/v2/backgroundworker/{id} is used to get the status of the generation of a search profile export file. The "{id}" is the guid of the background worker task, which is typically obtained when the export task is generated via the Assetic REST API endpoint POST /api/v2/search/{id}/export

The following statuses may be returned via the 'Status' property in the response:

The status of the export may also be viewed by navigating to the search profile in the Assetic application and selecting the 'Export' tab. This will show the history and status of all exports.

Sample payload:

https://abc.assetic.net/api/v2/backgroundworker/b616b2e6-ffbc-e611-946c-0667d62954d7

Sample response:

{  "Status":  "Completed",  "DocumentId":  "b816b5e6-ffbc-e611-945c-06edd62954d7"  }

Search Profile column mappings
The Assetic REST API endpoint GET /api/v2/search/{id}/metadata is used to get the following information about the given search profile guid "{id}".
Label: The name of each field as presented in the search.
Name: The internal Assetic field name which is abbreviated and does not contain whitespaces in the name.
Type: The datatype of the field. Typical values include:

• "System.String"
• "System.Nullable`[System.Double]"
• "System.Nullable`1[System.DateTime]"
• "System.Nullable`1[System.Decimal]"